package cn.itcast.goods.book.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.itcast.goods.book.domain.Book;
import cn.itcast.goods.pager.Expression;
import cn.itcast.goods.pager.PageBean;
import cn.itcast.goods.pager.PageConstants;
import cn.mytools.jdbc.TxQueryRunner;

/**
 * 图书模块持久层
 * @author HL
 *
 */
public class BookDao {
	private QueryRunner qr = new TxQueryRunner();
	
	/**
	 * 按分类查询
	 * @param cid
	 * @param pc
	 * @return
	 * @throws SQLException
	 */
	public PageBean<Book> findByCategory(String cid ,int pc) throws SQLException {
		List<Expression> expList = new ArrayList<Expression>();
		expList.add(new Expression("cid", "=", cid));
		return findByCriteria(expList, pc);
	}
	
	/**
	 * 按书名模糊查询
	 * @param bname
	 * @param pc
	 * @return
	 * @throws SQLException
	 */
	public PageBean<Book> findByBname(String bname ,int pc) throws SQLException {
		List<Expression> expList = new ArrayList<Expression>();
		expList.add(new Expression("bname", "like", "%"+bname+"%"));
		return findByCriteria(expList, pc);
	}
	
	/**
	 * 按作者模糊查询
	 * @param author
	 * @param pc
	 * @return
	 * @throws SQLException
	 */
	public PageBean<Book> findByAuthor(String author ,int pc) throws SQLException {
		List<Expression> expList = new ArrayList<Expression>();
		expList.add(new Expression("author", "like", "%"+author+"%"));
		return findByCriteria(expList, pc);
	}
	
	/**
	 * 按出版社模糊查询
	 * @param press
	 * @param pc
	 * @return
	 * @throws SQLException
	 */
	public PageBean<Book> findByPress(String press ,int pc) throws SQLException {
		List<Expression> expList = new ArrayList<Expression>();
		expList.add(new Expression("press", "like", "%"+press+"%"));
		return findByCriteria(expList, pc);
	}
	
	/**
	 * 多条件组合查询
	 * @param cid
	 * @param pc
	 * @return
	 * @throws SQLException
	 */
	public PageBean<Book> findByCombination(Book criteria ,int pc) throws SQLException {
		List<Expression> expList = new ArrayList<Expression>();
		expList.add(new Expression("bname", "like", "%"+criteria.getBname()+"%"));
		expList.add(new Expression("author", "like", "%"+criteria.getAuthor()+"%"));
		expList.add(new Expression("press", "like", "%"+criteria.getPress()+"%"));
		return findByCriteria(expList, pc);
	}
	
	/**
	 * 通用的查询
	 * @param expList
	 * @param pc
	 * @return
	 * @throws SQLException
	 */
	private PageBean<Book> findByCriteria(List<Expression> expList,int pc) throws SQLException {
		int ps = PageConstants.BOOK_PAGE_SIZE;//每页记录数
		
		StringBuilder whereSql = new StringBuilder(" where 1=1");
		List<Object> params = new ArrayList<Object>();//对应sql中的问号
		for(Expression exp : expList) {
			whereSql.append(" AND").append(" ").append(exp.getName())
			.append(" ").append(exp.getOperator()).append(" ");
			
			if(!exp.getOperator().equalsIgnoreCase("is null")) {
				whereSql.append("?");
				params.add(exp.getValue());
			}
		}
		//查询总记录数
		String sql = "select count(*) from t_book" + whereSql;
		Number number = (Number) qr.query(sql, new ScalarHandler(),params.toArray());
		int tr = number.intValue();//得到总记录数
		
		//按分页查询
		sql = "select * from t_book " +whereSql +" order by orderBy limit ? ,? " ;
		params.add((pc-1)*ps);
		params.add(ps);
		List<Book> beanList = qr.query(sql, new BeanListHandler<Book>(Book.class),params.toArray());
		
		PageBean<Book> pb = new PageBean<Book>();
		pb.setBeanList(beanList);
		pb.setPc(pc);
		pb.setPs(ps);
		pb.setTr(tr);
		//pagebean中的url由service完成
		return pb;
	}

	public Book findByBid(String bid) throws SQLException {
		String sql = "select * from t_book where bid = ?";
		Book book = qr.query(sql, new BeanHandler<Book>(Book.class),bid);
		return book;
	}

	public void edit(Book book) throws SQLException {
		String sql = "update t_book set bname=?,author=?,price=?,currPrice=?,discount=?,"
				+ "press=?,publishtime=?,edition=?,pageNum=?,wordNum=?,printtime=?,"
				+ "booksize=?,paper=?,cid=?,image_w=?,image_b=? where bid=?";
		Object[] params = {book.getBname(),book.getAuthor(),book.getPrice(),book.getCurrPrice(),
				book.getDiscount(),book.getPress(),book.getPublishtime(),book.getEdition(),
				book.getPageNum(),book.getWordNum(),book.getPrinttime(),book.getBooksize(),
				book.getPaper(),book.getCid(),book.getImage_w(),book.getImage_b(),book.getBid()};
		qr.update(sql,params);
		
	}

	public void delete(String bid) throws SQLException {
		String sql = "delete from t_book where bid = ?";
		qr.update(sql,bid);
	}

	public void add(Book book) throws SQLException {
		String sql = "insert into t_book(bid,bname,author,price,currPrice," +
				"discount,press,publishtime,edition,pageNum,wordNum,printtime," +
				"booksize,paper,cid,image_w,image_b)" +
				" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] params = {book.getBid(),book.getBname(),book.getAuthor(),
				book.getPrice(),book.getCurrPrice(),book.getDiscount(),
				book.getPress(),book.getPublishtime(),book.getEdition(),
				book.getPageNum(),book.getWordNum(),book.getPrinttime(),
				book.getBooksize(),book.getPaper(), book.getCid(),
				book.getImage_w(),book.getImage_b()};
		qr.update(sql, params);
	}
	
}
